package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * The DatabaseManager handles the connection with the Database and creates the
 * various tables upon creation. There can only be a single instance of this
 * object.
 * 
 * @author Sander Meijer </br>
 * 
 * 
 */
public class DatabaseManager {
	private static DatabaseManager uniqueInstance;
	private String driver = "org.apache.derby.jdbc.EmbeddedDriver";
	private String dbName = "jdbc:derby:AuctionHouse";
	private Connection conn;

	/**
	 * <b>Constructor</b></br> Private constructor, so that it cannot be called
	 * upon from the outside.
	 */
	private DatabaseManager() {
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(dbName);
			createUsers();
			CreateAuctionItem();
			CreateBids();
			System.out.println("Connected");
		} catch (Exception e) {
			try {
				Class.forName(driver);
				conn = DriverManager.getConnection(dbName + ";create=true");
				createUsers();
				CreateAuctionItem();
				CreateBids();
				System.out.println("Database created");
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}

	/**
	 * Static synchronized method to get the single instance of this object.
	 * 
	 * @return {@link DatabaseManager}
	 */
	public static synchronized DatabaseManager getInstance() {
		if (uniqueInstance == null)
			uniqueInstance = new DatabaseManager();
		return uniqueInstance;
	}

	/**
	 * Closes the connection.
	 */
	public void close() {
		try {
			conn = DriverManager.getConnection(dbName + ";shutdown=true");
		} catch (Exception e) {
		} finally {
			conn = null;
		}
	}

	/**
	 * Gets the single connection of the {@link DatabaseManager}
	 * 
	 * @return {@link Connection} conn
	 */
	public Connection getConnection() {
		return conn;
	}

	/**
	 * Method to empty the database of any data. Should only be used for testing purposes.
	 */
	public void clear() {
		try {
			conn.createStatement().execute("DROP TABLE AuctionBids");
			conn.createStatement().execute("DROP TABLE AuctionItem");
			conn.createStatement().execute("DROP TABLE Users");
			createUsers();
			CreateAuctionItem();
			CreateBids();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * Creates the user table, for more information refer to design
	 * documentation.
	 */
	private void createUsers() {
		try {
			conn.createStatement().execute("SELECT * FROM Users");
		} catch (SQLException e) {
			try {
				conn.createStatement()
						.execute(
								"CREATE TABLE Users ("
										+ "IDUser INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "
										+ "UserName VARCHAR(45) NOT NULL UNIQUE, "
										+ "Password VARCHAR(45) NOT NULL, "
										+ "PRIMARY KEY (IDUser)" + ")");
				// (>^.^)>
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
	}

	/**
	 * Creates the AuctionItem table, for more information refer to design
	 * documentation.
	 */
	private void CreateAuctionItem() {
		try {
			conn.createStatement().execute("SELECT * FROM AuctionItem");
		} catch (SQLException e) {
			try {
				conn.createStatement()
						.execute(
								"CREATE TABLE AuctionItem ("
										+ "IDAuctionItem INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "
										+ "Title VARCHAR(80) NOT NULL, "
										+ "Description CLOB (8 K), "
										+ "StartDate TIMESTAMP NOT NULL, "
										+ "StartPrice INT NOT NULL, "
										+ "EndTime TIMESTAMP NOT NULL, "
										+ "Owner INT NOT NULL, "
										+ "PRIMARY KEY (IDAuctionItem), "
										+ "CONSTRAINT Owner FOREIGN KEY (Owner) REFERENCES Users(IDUser)"
										+ ")");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
	}

	/**
	 * Creates the AuctionBids table, for more information refer to design
	 * documentation.
	 */
	private void CreateBids() {
		try {
			conn.createStatement().execute("SELECT * FROM AuctionBids");
		} catch (SQLException e) {
			try {
				conn.createStatement()
						.execute(
								"CREATE TABLE AuctionBids ("
										+ "BidValue INT NOT NULL, "
										+ "Time TIMESTAMP NOT NULL, "
										+ "AuctionID INT NOT NULL, "
										+ "UserID INT NOT NULL, "
										+ "CONSTRAINT AuctionID FOREIGN KEY (AuctionID) REFERENCES AuctionItem(IDAuctionItem), "
										+ "CONSTRAINT UserID FOREIGN KEY (UserID) REFERENCES Users(IDUser))");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
	}
}
